package l1j.server.server.datatables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import l1j.server.L1DatabaseFactory;
import l1j.server.server.utils.SQLUtil;

public class CheckTable {
	private static Logger _log = Logger.getLogger(CastleTable.class.getName());

	private static CheckTable _instance;

	public static CheckTable getInstance() {
		if (_instance == null) {
			_instance = new CheckTable();
		}
		return _instance;
	}

	private CheckTable() {
		load();
	}
	
	private int _Id;
	private int _startCount;
	private int _usedCount;
	private int _addCount;
	private int _nowCount;
	
	public int getAddCount(){
		return _addCount;
	}
	
	public void setAddCount(int i){
		_addCount = i;
	}
	
	public int getId(){
		return _Id;
	}
	
	public void setId(int i){
		_Id = i;
	}
	
	public int getStartCount(){
		return _startCount;
	}
	
	public void setStartCount(int i){
		_startCount = i;
	}
	
	public int getNowCount(){
		return _nowCount;
	}
	
	public void setNowCount(int i){
		_nowCount = i;
	}
	
	public int getUsedCount(){
		return _usedCount;
	}
	
	public void setUsedCount(int i){
		_usedCount = i;
	}
	private void load(){
		int totalcount;
		Vector<Object> item_List = loadInspectList();		
		for( int i =0 ; i< item_List.size(); i++){
			@SuppressWarnings("unchecked")			
			Vector<Object> item = (Vector<Object>) item_List.elementAt(i);
			int itemID = ((Number)item.elementAt(0)).intValue();
			String itemName = (String) item.elementAt(1);
			totalcount = loadCharItem(itemID,itemName)+loadWareItem(itemID,itemName)+loadClanItem(itemID,itemName)+loadElfWareItem(itemID,itemName);
			storeInspectCount(itemID,itemName,totalcount);
		}
	}
	
	private void loadInspectCount(int itemid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT id, startcount , usedcount, addcount, nowcount FROM sys_inspect_count WHERE itemid =? Order By date DESC limit 0, 1");
			pstm.setInt(1, itemid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				setId(rs.getInt("id"));
				setStartCount(rs.getInt("startcount"));
				setUsedCount(rs.getInt("usedcount"));
				setAddCount(rs.getInt("addcount"));
				setNowCount(rs.getInt("nowcount"));
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}

	
	public void storeInspectCount(int itemid, String itemName, int totalcount) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("INSERT INTO sys_inspect_count SET itemid=?, itemname=?, startcount=?, nowcount=?, date=SYSDATE()");
			pstm.setInt(1, itemid);
			pstm.setString(2, itemName);
			pstm.setInt(3, totalcount);
			pstm.setInt(4, totalcount);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void updateInspectCount(int itemid, int count) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("UPDATE sys_inspect_count SET usedcount=?, nowcount=? WHERE itemid=? and id=?");
			loadInspectCount(itemid);
			int startcount = getNowCount();			
			int usedcount = count + getUsedCount();
			int nowcount = startcount-count; 
			pstm.setInt(1, usedcount);
			pstm.setInt(2, nowcount);
			pstm.setInt(3, itemid);
			pstm.setInt(4, getId());
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void updateInspectAddCount(int itemid, int count) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("UPDATE sys_inspect_count SET addcount=?, nowcount=? WHERE itemid=? and id=?");
			loadInspectCount(itemid);
			int startcount = getNowCount();		
			int addcount = count + getAddCount();
			int nowcount = startcount+count; 
			pstm.setInt(1, addcount);			
			pstm.setInt(2, nowcount);
			pstm.setInt(3, itemid);
			pstm.setInt(4, getId());
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	private int loadCharItem(int itemid, String itemName) {
		int totalcount = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT count , char_id FROM character_items WHERE item_id =?");
			pstm.setInt(1, itemid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				totalcount += rs.getInt("count");
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return totalcount;
	}
	
	private int loadWareItem(int itemid, String itemName) {
		int totalcount = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;	
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT count , account_name FROM character_warehouse WHERE item_id = ?");
			pstm.setInt(1, itemid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				totalcount += rs.getInt("count");
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return totalcount;
	}
	
	private int loadClanItem(int itemid, String itemName) {
		int totalcount = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;	
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT count , clan_name FROM clan_warehouse WHERE item_id = ?");
			pstm.setInt(1, itemid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				totalcount += rs.getInt("count");
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return totalcount;
	}
	
	private int loadElfWareItem(int itemid, String itemName) {
		int totalcount = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;	
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT count , account_name FROM character_elf_warehouse WHERE item_id = ?");
			pstm.setInt(1, itemid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				totalcount += rs.getInt("count");
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return totalcount;
	}
	
	private Vector<Object> loadInspectList() {
		Vector<Object> item_List = new Vector<Object>();
		Vector<Object> rs_List = new Vector<Object>();
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null; 
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM sys_inspect_list");
			rs = pstm.executeQuery();
		   
			while (rs.next()) {
				rs_List.add(rs.getInt("itemid"));
				rs_List.add(rs.getString("itemname"));
				item_List.add(rs_List);
				rs_List = new Vector<Object>();
			}
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return item_List;
	}
}
